/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package gleonWebService;

import javax.jws.WebService;
import java.rmi.RemoteException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.List;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;
import javax.sql.DataSource;





/**
 * SOAP Web service to serve up GLEON central data. Used by VADER and other tools
 * to get data from GLEON central.
 *
 * @author lawinslow
 */
@WebService(name="GleonData")
public class gleonDataService {

    private DataSource pool = null;

    private static Connection cachedConnection = null;
    private static Date cachedDate = new Date();

    public VegaStreamContainer getStreamData(Date start,Date end,int streamID)throws RemoteException{
        return getStreamDataInternal(start,end,streamID,getDatabaseConnection());
    }

    public VegaStreamContainer getStreamDataCSV(Date start,Date end,int streamID)throws RemoteException{
        Connection conn = getDatabaseConnection();
        
        VegaStreamContainer stream = getStreamDataInternal(start,end,streamID,conn);


        StringBuilder sb = new StringBuilder();

        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

        for(int i=0;i<stream.datetime.size();i++){
            sb.append(outputFormat.format(stream.datetime.get(i)));
            sb.append(",");
            sb.append(stream.values.get(i));
            sb.append("\n");
        }

        //save csv text in stream container
        stream.csv = sb.toString();

        //remove the values, flags, and datetime lists. We do NOT want to pass
        // these. Some SOAP clients don't deal well with long lists (e.g., Matlab)
        stream.values = null;
        stream.flags = null;
        stream.datetime = null;

        return stream;

    }

    public VegaStreamContainer getIndividualStream(
            Date start, Date end, String site, String variable,
            String unit, int aggSpanInSec,String aggMethod,
            int repID,double offsetValue, String offsetType)throws RemoteException{


       
        int streamID = getStreamID(site,variable,unit,aggSpanInSec,
                aggMethod,repID,offsetValue,offsetType);
        return getStreamData(start,end,streamID);


    }
    
    public VegaStreamContainer getIndividualStreamNoOffset(
            Date start, Date end,String site,String variable, String unit,
            int aggSpanInSec,String aggMethod, int repID)throws RemoteException{
        
        return getIndividualStream(start, end,site,variable,unit,
                aggSpanInSec,aggMethod,repID,Double.NaN,null);
    }

    public VegaStreamContainer getIndividualStreamCSVNoOffset(
            Date start, Date end,String site,String variable, String unit,
            int aggSpanInSec,String aggMethod, int repID)throws RemoteException{

        int streamID = getStreamID(site,variable,unit,aggSpanInSec,
                aggMethod,repID,Double.NaN,null);
        return getStreamDataCSV(start,end,streamID);
    }

    public VegaStreamContainer getIndividualStreamCSV(
            Date start, Date end,String site,String variable, String unit,
            int aggSpanInSec,String aggMethod, int repID,
            double offsetValue,String offsetType)throws RemoteException{

        Connection conn = getDatabaseConnection();

        int streamID = getStreamIDInternal(site,variable,unit,aggSpanInSec,
                aggMethod,repID,offsetValue,offsetType,conn);
        
        return getStreamDataCSV(start,end,streamID);
    }

    public int getStreamID(String site, String variable,
            String unit, int aggSpanInSec,String aggMethod,
            int repID,double offsetValue, String offsetType)throws RemoteException{

        Connection conn = this.getDatabaseConnection();
        try{
            return getStreamIDInternal(site,variable,unit,aggSpanInSec,aggMethod,
                    repID,offsetValue,offsetType,conn);
        }catch(RemoteException e){
            throw e;
        }
        
    }

    public VegaStreamContainer getStreamMetadata(int streamID)throws RemoteException{
        return getStreamMetadataInternal(streamID, getDatabaseConnection());
    }

    public int getVariableID(String variable)throws RemoteException{
        Connection conn = getDatabaseConnection();
        try{
            String variableSql = "SELECT VariableID FROM Variables " +
                    " WHERE VariableName=?";

            PreparedStatement varStatement = conn.prepareStatement(variableSql);

            varStatement.setString(1, variable);

            ResultSet rs = varStatement.executeQuery();

            if(!rs.next()){
                throw new RemoteException(variable + " is not a valid" +
                        " variable in the database. Please verify.");
            }

            return rs.getInt("VariableID");

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);

        }
    }
    public int getSiteID(String site)throws RemoteException{

        try{
            String siteSql = "SELECT SiteID FROM Sites " +
                    " WHERE SiteName=?";

            PreparedStatement siteStatement = getDatabaseConnection().prepareStatement(siteSql);

            siteStatement.setString(1, site);

            ResultSet rs = siteStatement.executeQuery();

            if(!rs.next()){
                throw new RemoteException(site + " is not a valid" +
                        " site in the database. Please verify.");
            }

            return rs.getInt("SiteID");

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);

        }
    }
    public int getUnitID(String unit)throws RemoteException{
        try{
            String unitSql = "SELECT UnitID FROM Units " +
                    " WHERE UnitShort=?";

            PreparedStatement unitStatement = getDatabaseConnection().prepareStatement(unitSql);

            unitStatement.setString(1, unit);

            ResultSet rs = unitStatement.executeQuery();

            if(!rs.next()){
               throw new RemoteException(unit + " is not a valid" +
                        " unit in the database. Please verify.");
            }
            
            int tmp = rs.getInt("UnitID");

            rs.close();

            return tmp;

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);

        }
    }
    public int getAggMethodID(String aggMethod)throws RemoteException{
        try{
            String methodSql = "SELECT AggMethodID FROM AggMethods " +
                    " WHERE AggMethod=?";

            PreparedStatement methodStatement = getDatabaseConnection().prepareStatement(methodSql);

            methodStatement.setString(1,aggMethod);

            ResultSet rs = methodStatement.executeQuery();

            if(!rs.next()){
                throw new RemoteException(aggMethod + " is not a valid" +
                        " agg method in the database. Please verify.");
            }
            int tmp = rs.getInt("AggMethodID");

            rs.close();

            return tmp;

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);

        }
    }
    public int getSourceID(String source)throws RemoteException{
        try{
            String sourceSql = "SELECT SourceID FROM Sources " +
                    " WHERE SourceName=?";

            PreparedStatement sourceStatement = getDatabaseConnection().prepareStatement(sourceSql);

            sourceStatement.setString(1,source);

            ResultSet rs = sourceStatement.executeQuery();

            if(!rs.next()){
                throw new RemoteException(source + " is not a valid" +
                        " source in the database. Please verify.");
            }

            int tmp = rs.getInt("SourceID");

            rs.close();

            return tmp;

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);

        }
    }
    public int getOffsetTypeID(String offsetType)throws RemoteException{

        try{
            String offsetSql = "SELECT OffsetType FROM OffsetTypes " +
                    " WHERE OffsetName=?";

            PreparedStatement offsetStatement = getDatabaseConnection().prepareStatement(offsetSql);

            offsetStatement.setString(1,offsetType);

            ResultSet rs = offsetStatement.executeQuery();

            if(!rs.next()){
                throw new RemoteException(offsetType + " is not a valid" +
                        " offset type in the database. Please verify.");
            }

            int tmp = rs.getInt("OffsetType");

            rs.close();

            return tmp;

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);
        }
    }
    public List<String> getSites()throws RemoteException{

        try{
            String offsetSql = "SELECT `SiteName` FROM `Sites` " +
                    "WHERE " +
                    "`SpecialProject` = 0 " +
                    "ORDER BY `SiteName`";

            PreparedStatement offsetStatement = getDatabaseConnection().prepareStatement(offsetSql);

            ResultSet rs = offsetStatement.executeQuery();

            ArrayList<String> sites = new ArrayList<String>();

            while(rs.next())
                sites.add(rs.getString("SiteName"));

            rs.close();

            return sites;

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);
        }

    }
    public List<String> getVariables()throws RemoteException{

        try{
            String offsetSql = "SELECT VariableName FROM Variables " +
                    " WHERE IsDepricated=FALSE " +
                    " ORDER BY VariableName ";

            PreparedStatement offsetStatement = getDatabaseConnection().prepareStatement(offsetSql);

            ResultSet rs = offsetStatement.executeQuery();



            ArrayList<String> variables = new ArrayList<String>();

            while(rs.next())
                variables.add(rs.getString("VariableName"));

            rs.close();

            return variables;

        }catch(java.sql.SQLException ex){
            throw new RemoteException(
                    "Unknown exception while accessing database.",ex);
        }
    }
    public List<VegaStreamContainer> getAllStreamMetadata()throws RemoteException{
        Connection conn = getDatabaseConnection();

        try{
            ArrayList<VegaStreamContainer> allStreams = new ArrayList<VegaStreamContainer>();
            PreparedStatement streams = conn.prepareStatement(
                    "SELECT StreamID FROM Streams " +
                    "JOIN Sites on Streams.SiteID=Sites.SiteID " +
                    "JOIN (SELECT * FROM Variables WHERE IsDepricated=FALSE) as var " +
                    " on var.VariableID = Streams.VariableID " +
                    " ORDER BY SiteName,VariableName,OffsetValue");

            ResultSet rs = streams.executeQuery();

            while(rs.next()){
                allStreams.add(this.getStreamMetadataInternal(rs.getInt("StreamID"),conn));
            }

            rs.close();
            
            return allStreams;
        }catch(java.sql.SQLException e){
            throw new RemoteException("Unknown DB connection problem",e);
        }

    }
    

    /***************************************************************************
     * INTERNAL METHOD SECTION
     **************************************************************************/
    
    private String formatAggSpan(int secs){
        String hours = String.valueOf(secs/(60*60));
        String minutes = String.valueOf((secs%(60*60))/60);
        String seconds = String.valueOf(secs%60);

        hours = (hours.length() < 2)?"0"+hours:hours;
        minutes = (minutes.length() < 2)?"0"+minutes:minutes;
        seconds = (seconds.length() < 2)?"0"+seconds:seconds;

        return hours+":"+minutes+":"+seconds;
    }
    private Connection getDatabaseConnection()throws RemoteException{
        //if we have a cachedConnection and it isn't older 1/2 hour
        if(cachedConnection!=null && (new Date()).getTime() - cachedDate.getTime() < (30*60*1000)){
            return cachedConnection;
        }
        
        cachedDate = new Date();
        Connection conn;
        try{
            Context env = null;
            try{
                env=(Context) (new InitialContext());
                
                pool=(DataSource)env.lookup("jdbc/vega");
                if(pool==null){
                    throw new RemoteException("unknown DataSource");
                }
            }catch(NamingException ne){
                throw new RemoteException(ne.getMessage());
            }
            conn = pool.getConnection();
            cachedConnection = conn;
            return conn;
        }catch(Exception ex){
            throw new RemoteException("DB connection closed, error while reconnecting.",ex);
        }
    }
    private VegaStreamContainer getStreamMetadataInternal(int streamID, Connection conn) throws RemoteException{

        try{
            VegaStreamContainer toReturn = new VegaStreamContainer();
            toReturn.streamID = streamID;

            PreparedStatement streamInfo = conn.prepareStatement(
                    "SELECT MAX(`DateTime`) as max,MIN(`DateTime`) as min FROM " +
                    "`Values` WHERE StreamID = ?");

            streamInfo.setInt(1, streamID);

            ResultSet rs = streamInfo.executeQuery();

            if(rs.next() & rs.getDate("min") != null){
               toReturn.earliestDate = new Date(rs.getDate("min").getTime());
               toReturn.latestDate = new Date(rs.getDate("max").getTime());
            }else{
                toReturn.hasNoValues = true;
            }

            rs.close();
            streamInfo.close();

            streamInfo = conn.prepareStatement("select SiteName,VariableName,MethodID,SourceName, "+
                "OffsetName,OffsetValue,UnitShort as UnitName,TIME_TO_SEC(AggSpan) as AggSpanInSec,AggMethod " +
                "from Streams " +
                "left join AggMethods on AggMethods.AggMethodID = Streams.AggMethodID "+
                "left join OffsetTypes on OffsetTypes.OffsetType = Streams.OffsetType "+
                "left join Sites on Sites.SiteID = Streams.SiteID "+
                "left join Sources on Sources.SourceID = Streams.SourceID "+
                "left join Units on Streams.UnitID = Units.UnitID "+
                "left join "+
                "(SELECT * FROM Variables WHERE IsDepricated = FALSE) as var"+
                " on var.VariableID = Streams.VariableID "+
                "WHERE StreamID = ?");
            streamInfo.setInt(1, streamID);
            rs = streamInfo.executeQuery();

            if(rs.next()){
                try{
                toReturn.AggMethod = rs.getString("AggMethod");
                toReturn.AggSpanInSec = rs.getInt("AggSpanInSec");
                toReturn.method = rs.getInt("MethodID");
                toReturn.offsetType = rs.getString("OffsetName");
                toReturn.offsetValue = rs.getDouble("OffsetValue");
                toReturn.site = rs.getString("SiteName");
                toReturn.source = rs.getString("SourceName");
                toReturn.unit = rs.getString("UnitName");
                toReturn.variable = rs.getString("VariableName");
                }catch(Exception e){
                    e.printStackTrace();
                }
            }else{
                toReturn.hasMetadataException = true;
            }
            rs.close();
            streamInfo.close();

            return toReturn;
        }catch(java.sql.SQLException se){
            throw new RemoteException("Exception while querying database.", se);
        }
    }
    private VegaStreamContainer getStreamDataInternal(Date start,Date end,int streamID,Connection conn)throws RemoteException{

        //get a stream object populated with the metadata for this streamid
        VegaStreamContainer stream = getStreamMetadataInternal(streamID,conn);
        try{

            PreparedStatement retrieveValues = conn.prepareStatement(
                    "SELECT `DateTime`,`Value`,`Flag` FROM `Values` " +
                    " WHERE `StreamID` = ? AND " +
                    " `DateTime` >=? AND " +
                    " `DateTime` <= ? ORDER BY `DateTime`");

            retrieveValues.setInt(1,streamID);
            retrieveValues.setTimestamp(2, new Timestamp(start.getTime()));
            retrieveValues.setTimestamp(3, new Timestamp(end.getTime()));

            ResultSet rs = retrieveValues.executeQuery();

            ArrayList<Date> dateTime = new ArrayList<Date>();
            ArrayList<Double> values = new ArrayList<Double>();
            ArrayList<String> flags = new ArrayList<String>();

            while(rs.next()){
                dateTime.add(new Date(rs.getTimestamp("DateTime").getTime()));
                values.add(rs.getDouble("Value"));
                flags.add(rs.getString("Flag"));
            }

            rs.close();
            retrieveValues.close();

            stream.datetime = dateTime;
            stream.values = values;
            stream.flags = flags;


        }catch(Exception e){
            throw new RemoteException("Unknown issue while querying database.",e);
        }finally{
        }

        return stream;
    }
    private int getStreamIDInternal(String site, String variable,
                    String unit, int aggSpanInSec,String aggMethod,
                    int repID,double offsetValue, String offsetType,
                    Connection conn)throws RemoteException{

        
        int siteID = getSiteID(site);
        int variableID = getVariableID(variable);
        int unitID = getUnitID(unit);
        int aggMethodID = getAggMethodID(aggMethod);


        try{

            //Prepare the aggSpan string in the proper format
            String aggSpanStr = formatAggSpan(aggSpanInSec);

            //Start building the SQL Query
            String streamSql = "SELECT StreamID FROM Streams ";

            //Add the required where clauses
            streamSql += " WHERE " +
                    " SiteID=? AND " +
                    " VariableID=? AND " +
                    " UnitID=? AND " +
                    " AggMethodID=? AND " +
                    " AggSpan='" + aggSpanStr +"' AND" +
                    " RepID=? ";


            int offsetTypeID = -1;
            if(offsetValue != Double.NaN && !(offsetValue < 0) &&
                    offsetType != null && offsetType.compareTo("")!=0){
                offsetTypeID = getOffsetTypeID(offsetType);
                streamSql += " AND OffsetValue=? AND OffsetType=? ";
            }


            PreparedStatement streamStatement = conn.prepareStatement(streamSql);

            streamStatement.setInt(1, siteID);
            streamStatement.setInt(2, variableID);
            streamStatement.setInt(3, unitID);
            streamStatement.setInt(4, aggMethodID);
            streamStatement.setInt(5, repID);

            if(offsetValue != Double.NaN && !(offsetValue < 0) &&
                    offsetType != null && offsetType.compareTo("")!=0){
                streamStatement.setDouble(6, offsetValue);
                streamStatement.setInt(7, offsetTypeID);
            }

            //issue query
            ResultSet rs = streamStatement.executeQuery();
            int count = 0;
            int id = 0;
            while(rs.next()){
                count+=1;
                id = rs.getInt("StreamID");
            }
            rs.close();

            if(count==1){
                return id;
            }else if(count == 0){
                throw new RemoteException("No StreamID found with the " +
                        "supplied metadata.");
            }else{
                throw new RemoteException("Ambiguous stream definition." +
                        " Cannot reliably determine StreamID");
            }

        }catch(java.sql.SQLException e){
            throw new RemoteException(
                    "Unknown exception while accessing database.",e);
        }


    }
}
